﻿#include "IDatabase.h"
#include <QUuid>
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>
#include <QJsonDocument>
#include <QJsonObject>
#include <QJsonArray>


IDatabase::IDatabase(QObject *parent)
    : QObject{parent}
{
    qRegisterMetaType<SqlLinkParam>("SqlLinkParam");
}

IDatabase::IDatabase(const SqlLinkParam &par, QObject *parent):QObject(parent)
    ,mLinkPar(par)
{

}

IDatabase::~IDatabase()
{
    mDb.close();
}

void IDatabase::setSqlState(SqlState state)
{
    if(state == mState)
        return;
    mState = state;
}

void IDatabase::setSqlError(SqlError err)
{
    if(err == mErr)
        return;
    mErr = err;
}

QString IDatabase::getUuid()
{
    return QUuid::createUuid().toString();
}

void IDatabase::checkErrType(QSqlQuery query)
{
    if(query.lastError().type() == QSqlError::ConnectionError){
        setSqlState(SS_Invalid);
    }
}


bool IDatabase::createRow(const QJsonObject &jsonObject)
{
    QSqlQuery query(mDb);

    QString sqlStatement = QString("INSERT  INTO %1 (%2, %3, %4, %5) VALUES (:%2, :%3, :%4, :%5)").arg(mLinkPar.tableName,
                                                                                                       DBUuid,
                                                                                                       DBCreatTime,
                                                                                                       DBModifyTime,
                                                                                                       DBJson);
    query.prepare(sqlStatement);

    query.bindValue(QString(":%1").arg(DBUuid), jsonObject.value(DBUuid));
    query.bindValue(QString(":%1").arg(DBCreatTime), jsonObject.value(DBCreatTime));
    query.bindValue(QString(":%1").arg(DBModifyTime), jsonObject.value(DBModifyTime));
    query.bindValue(QString(":%1").arg(DBJson), QJsonDocument(jsonObject).toJson(QJsonDocument::Compact));

    if(!query.exec()) {
        setSqlError(SE_Insert);
        mErrMsg = "createRow " + query.lastError().text() + " " + sqlStatement;
        checkErrType(query);
        return false;
    }

    return true;
}

bool IDatabase::deleteRowByDateTime(const QDateTime &dateTimeBegin, const QDateTime &dateTimeEnd)
{
    QSqlQuery query(mDb);

    QString sqlStatement = QString("DELETE FROM %1 WHERE %2 between %3 and %4").arg(mLinkPar.tableName,
                                                                                    DBCreatTime,
                                                                                    dateTimeBegin.toString(DBDateTimeFormat),
                                                                                    dateTimeEnd.toString(DBDateTimeFormat));
    if(!query.exec(sqlStatement)){
        mErrMsg = "deleteRowByDateTime " + query.lastError().text() + " " + sqlStatement;
        checkErrType(query);
        return false;
    }
    return query.numRowsAffected() > 0;
}

bool IDatabase::deleteRowByUuid(const QString &uuid)
{
    QSqlQuery query(mDb);
    QString sqlStatement = QString("DELETE FROM %1 WHERE %2 = '%3'").arg(mLinkPar.tableName, DBUuid, uuid);
    if(!query.exec(sqlStatement)){
        mErrMsg = "deleteRowByUuid " + query.lastError().text() + " " + sqlStatement;
        checkErrType(query);
        return false;
    }
    return true;
}

bool IDatabase::findRowsByData(const QString &searchText, QJsonArray &datas)
{
    if (searchText.isEmpty()) return false;
    QSqlQuery query(mDb);
    QString sqlStatement = QString("SELECT %1 FROM %2 where lower(%3) like :%4").arg(DBJson, mLinkPar.tableName, DBJson, DBJson);
    query.prepare(sqlStatement);
    query.bindValue(QString(":%1").arg(DBJson), QVariant("%" + searchText.toLower() + "%"));
    if (!query.exec()){
        mErrMsg = "findRowsByData " + query.lastError().text() + " " + sqlStatement;
        checkErrType(query);
        return false;
    };

    QJsonArray returnValue;

    while ( query.next() ) {
        auto json = query.value(0).toByteArray();
        auto jsonDocument = QJsonDocument::fromJson(json);
        if (jsonDocument.isObject()) {
            returnValue.append(jsonDocument.object());
        }
    }
    datas = returnValue;
    return true;
}

bool IDatabase::findRowsByDateTime(const QDateTime &dateTimeBegin, const QDateTime &dateTimeEnd, QJsonArray &datas)
{
    if (dateTimeEnd < dateTimeBegin)
        return false;
    QSqlQuery query(mDb);
    QString sqlStatement = QString("SELECT %1 FROM %2 where %3 between %4 and %5").arg(DBJson,
                                                                                       mLinkPar.tableName,
                                                                                       DBCreatTime,
                                                                                       dateTimeBegin.toString(DBDateTimeFormat),
                                                                                       dateTimeEnd.toString(DBDateTimeFormat));
    if (!query.exec(sqlStatement)){
        mErrMsg = "findRowsByDateTime " + query.lastError().text() + " " + sqlStatement;
        checkErrType(query);
        return false;
    };

    QJsonArray returnValue;

    while ( query.next() ) {
        auto json = query.value(0).toByteArray();
        auto jsonDocument = QJsonDocument::fromJson(json);
        if (jsonDocument.isObject()) {
            returnValue.append(jsonDocument.object());
        }
    }
    datas = returnValue;
    return true;
}

bool IDatabase::findRowByDateTime(const QDateTime &dateTime, QJsonObject &jsonData)
{
    QSqlQuery query(mDb);
    QString sqlStatement = QString("SELECT %1 FROM %2 where %3 = '%4'").arg(DBJson,
                                                                          mLinkPar.tableName,
                                                                          DBCreatTime,
                                                                          dateTime.toString(DBDateTimeFormat));
    if (!query.exec(sqlStatement)){
        mErrMsg = "findRowByDateTime " + query.lastError().text() + " " + sqlStatement;
        checkErrType(query);
        return false;
    };
    if (query.next()){
        auto json = query.value(0).toByteArray();
        auto jsonDocument = QJsonDocument::fromJson(json);
        if (jsonDocument.isObject()) {
            jsonData = jsonDocument.object();
        }
    }
    return true;
}

bool IDatabase::findRowByUuid(const QString &uuid, QJsonObject &jsonData)
{
    QSqlQuery query(mDb);
    QString sqlStatement = QString("SELECT %1 FROM %1 where %2 = '%3'").arg(DBJson,
                                                                          mLinkPar.tableName,
                                                                          DBUuid,
                                                                          uuid);
    if (!query.exec(sqlStatement)){
        mErrMsg = "findRowByUuid " + query.lastError().text() + " " + sqlStatement;
        checkErrType(query);
        return false;
    };
    if (query.next()){
        auto json = query.value(0).toByteArray();
        auto jsonDocument = QJsonDocument::fromJson(json);
        if (jsonDocument.isObject()) {
            jsonData = jsonDocument.object();
        }
    }
    return true;
}

bool IDatabase::readAllRows(QJsonArray &datas)
{
    QSqlQuery query(mDb);
    QString sqlStatement = QString("SELECT %1 FROM %2").arg(DBJson, mLinkPar.tableName);
    if (!query.exec(sqlStatement)) {
        mErrMsg = "readAllRows " + query.lastError().text() + " " + sqlStatement;
        checkErrType(query);
        return false;
    }
    QJsonArray returnValue;
    while ( query.next() ) {
        auto json = query.value(0).toByteArray();
        auto jsonDocument = QJsonDocument::fromJson(json);
        if (jsonDocument.isObject()) {
            returnValue.append(jsonDocument.object());
        }
    }
    datas = returnValue;
    return true;
}

bool IDatabase::updateRow(const QJsonObject &jsonObject)
{
    // qDebug() << jsonObject;
    if (jsonObject.isEmpty()) return false;
    QSqlQuery query(mDb);
    QString sqlStatement = QString("UPDATE %1 SET %2=:%2, %3=:%3 WHERE %4=:%4").arg(mLinkPar.tableName,
                                                                                    DBModifyTime,
                                                                                    DBJson,
                                                                                    DBUuid);
    query.prepare(sqlStatement);

    query.bindValue(QString(":%1").arg(DBUuid), jsonObject.value(DBUuid).toString());
    query.bindValue(QString(":%1").arg(DBModifyTime), jsonObject.value(DBModifyTime).toString());
    query.bindValue(QString(":%1").arg(DBJson), QVariant(QJsonDocument(jsonObject).toJson(QJsonDocument::Compact)));

    if(!query.exec()) {
        mErrMsg = "updateRow " + query.lastError().text() + " " + sqlStatement;
        checkErrType(query);
        return false;
    }
    return true;
}

